package com.mbc.common.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


import com.mbc.common.entities.SystemSetting;
import com.mbc.common.data.SystemSettingDataList;
import com.mbc.common.param.CriteriaParams;

public class SystemSettingDAOImpls extends DAO<SystemSetting> implements SystemSettingDAO {

	public SystemSettingDAOImpls(Connection conn)
	{
		this.conn = conn;
		this.table_name = "SystemSetting";
	}

	@Override
	public void create(SystemSetting entity) throws SQLException {
		query = "insert into " + table_name + " ( code, t1, t2, t3, t4, t5, n1, n2, n3, n4, n5) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
				pstmt.setString(i++, entity.getcode());
		pstmt.setString(i++, entity.gett1());
		pstmt.setString(i++, entity.gett2());
		pstmt.setString(i++, entity.gett3());
		pstmt.setString(i++, entity.gett4());
		pstmt.setString(i++, entity.gett5());
		pstmt.setInt(i++, entity.getn1());
		pstmt.setInt(i++, entity.getn2());
		pstmt.setInt(i++, entity.getn3());
		pstmt.setInt(i++, entity.getn4());
		pstmt.setInt(i++, entity.getn5());

		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(SystemSetting entity) throws SQLException {
		query = "update " + table_name + " set  code = ?, t1 = ?, t2 = ?, t3 = ?, t4 = ?, t5 = ?, n1 = ?, n2 = ?, n3 = ?, n4 = ?, n5 = ?";
		pstmt = conn.prepareStatement(query);
		int i = 1;
				pstmt.setString(i++, entity.getcode());
		pstmt.setString(i++, entity.gett1());
		pstmt.setString(i++, entity.gett2());
		pstmt.setString(i++, entity.gett3());
		pstmt.setString(i++, entity.gett4());
		pstmt.setString(i++, entity.gett5());
		pstmt.setInt(i++, entity.getn1());
		pstmt.setInt(i++, entity.getn2());
		pstmt.setInt(i++, entity.getn3());
		pstmt.setInt(i++, entity.getn4());
		pstmt.setInt(i++, entity.getn5());

		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery();
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public SystemSetting find(long key) throws SQLException {
		SystemSetting l_SystemSetting = null;
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_SystemSetting = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_SystemSetting;
	}

	@Override
	public ArrayList<SystemSetting> findAll() throws SQLException {
		ArrayList<SystemSetting> l_SystemSettingList = new ArrayList<SystemSetting>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_SystemSettingList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_SystemSettingList;
	}

	@Override
	protected SystemSetting readRecord(ResultSet rs) throws SQLException {
		SystemSetting l_entity = new SystemSetting();
				l_entity.setId(rs.getLong("Id"));
		l_entity.setcode(rs.getString("code"));
		l_entity.sett1(rs.getString("t1"));
		l_entity.sett2(rs.getString("t2"));
		l_entity.sett3(rs.getString("t3"));
		l_entity.sett4(rs.getString("t4"));
		l_entity.sett5(rs.getString("t5"));
		l_entity.setn1(rs.getInt("n1"));
		l_entity.setn2(rs.getInt("n2"));
		l_entity.setn3(rs.getInt("n3"));
		l_entity.setn4(rs.getInt("n4"));
		l_entity.setn5(rs.getInt("n5"));

		return l_entity;
	}

	public SystemSettingDataList getList(CriteriaParams cri) throws SQLException{
		SystemSettingDataList l_DataList = new SystemSettingDataList();
		ArrayList<SystemSetting> l_entityList = new ArrayList<SystemSetting>();
		String l_cri = "";
		query = "set rowcount " + cri.getSize() +
			" select * from (select row_number() over(order by Id) as [rowno], * from " + table_name +
			" where 1 = 1" + l_cri + ") " + table_name +
			" where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()) {
			do {	
				l_entityList.add(readRecord(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(Id) from " + table_name + " where 1 = 1" + l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
    }
}
